﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NPSoft.Ferrero.DataBridge.Model;
using NPSoft.Ferrero.Framework.DAL;

namespace NPSoft.Ferrero.DataBridge.DAL
{
    public class ProductUtil : IDAL<Product>
    {
        public int Add(Product info)
        {
            throw new NotImplementedException();
        }

        public int Edit(Product info)
        {
            throw new NotImplementedException();
        }

        public Product GetById(long id)
        {
            throw new NotImplementedException();
        }

        public Product GetById(Guid id)
        {
            throw new NotImplementedException();
        }

        public int Remove(Product info)
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet Search(StringBuilder condtion)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                return SqlHelper.GetConnection();
            }
        }

        public ICollection<Product> SearchCollection(StringBuilder condtion)
        {
            ICollection<Product> infos = new List<Product>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"
               SELECT  
                PRD.PRD_CD, PRD.PRD_DESC2, PRD.PRD_DESC1, PRD.PRD_DESC, PRD.CMCL_CD, PRD.CMCL_NM_CHI, PRD.CMCL_NM_ENG,
                        PRD.SHELF_LIFE, PRD.PCS_CU, ISNULL(dbo.UFN_GET_SPLIT_PACKING(PRD.PACKING, 1), 1) AS CU_OUTER,
                        ISNULL(dbo.UFN_GET_SPLIT_PACKING(PRD.PACKING, 2), 1) AS OUTER_CTN,
                        CONVERT(INT, ISNULL(dbo.UFN_GET_SPLIT_PACKING(PRD.PACKING, 1), 1))
                        * CONVERT(INT, ISNULL(dbo.UFN_GET_SPLIT_PACKING(PRD.PACKING, 2), 1)) AS CU_CTN, PRD.FISCAL_YEAR, PRD.SALES_ORG,
                        PRD.TYPE, PRD.QS_CIQ, PRD.SOURCE, PRD.PLANT,
                        dbo.udf_Get_ConvertFactor_To_CU(PRD.PRD_CD, 'PAL') / CASE dbo.udf_Get_ConvertFactor_To_CU(PRD.PRD_CD, 'TU')
                                                                               WHEN 0 THEN 1
                                                                               ELSE dbo.udf_Get_ConvertFactor_To_CU(PRD.PRD_CD, 'TU')
                                                                             END AS CTN_PL, dbo.UFN_GET_VOLUME_TU(PRD.PRD_CD) AS VOLUME,
                        PRD.STATUS, PRD.PROMO_ID, PACK.TADE_PACKAGING_CODE, PACK.TADE_PACKAGING_SEL_DESCR,
                        dbo.UFN_GET_BARCODE(PRD.PRD_CD, 'CU') AS 'Barcode_CU(EA)',
                        dbo.UFN_GET_BARCODE(PRD.PRD_CD, 'TU') AS 'Barcode_SU(Outer)',
                        dbo.UFN_GET_BARCODE(PRD.PRD_CD, 'CT') AS 'Barcode_CU(CartON)', PRD.PRDCAT_KEY AS SUBCLASS,
                        SUBC.PRDCAT_DESC AS subc_desc,--4
                        brand.PRDCAT_CD AS brand, brand.PRDCAT_DESC AS brand_desc, --3
                        bg.PRDCAT_CD AS brandGroup, bg.PRDCAT_DESC AS brandGroup_desc,--2
                        ca.PRDCAT_CD AS category, ca.PRDCAT_DESC AS category_desc--1
                        , REF_VALUE2 AS 'BRAND_NickName_Code', '' AS 'BRAND_NickName_Desc', dbo.UFN_GET_QLI_TU(PRD.PRD_CD) AS Qli,
                        dbo.UFN_GET_WEIGHT_BY_INDICATOR(PRD.PRD_CD, 'TU', 1) AS TU_PRINT_NET_WEIGHT,
                        dbo.UFN_GET_WEIGHT_BY_INDICATOR(PRD.PRD_CD, 'CU', 1) AS CU_PRINT_NET_WEIGHT,
                        dbo.UFN_GET_WEIGHT_BY_INDICATOR(PRD.PRD_CD, 'TU', 2) AS TU_NET_WEIGHT,
                        dbo.UFN_GET_WEIGHT_BY_INDICATOR(PRD.PRD_CD, 'CU', 2) AS CU_NET_WEIGHT,
                        dbo.UFN_GET_WEIGHT_BY_INDICATOR(PRD.PRD_CD, 'TU', 3) AS TU_GROSS_WEIGHT,
                        dbo.UFN_GET_WEIGHT_BY_INDICATOR(PRD.PRD_CD, 'CU', 3) AS CU_GROSS_WEIGHT
                FROM    MST_PRD AS PRD
                LEFT JOIN MST_PRDUOM AS UOM
                ON      PRD.PRD_CD = UOM.PRD_CD AND ISNULL(UOM.UOM_CD,'') <> ''
                LEFT JOIN MST_PRD_PACKING AS PACK
                ON      PRD.PACKING = PACK.TADE_PACKAGING_CODE
                LEFT JOIN MST_PRDCAT AS SUBC
                ON      PRDCAT_CD = PRDCAT_KEY AND SUBC.PRDCAT_LEVEL = 4
                LEFT JOIN MST_PRDCAT AS brand
                ON      SUBC.PRDCAT_PAR = brand.PRDCAT_CD AND brand.PRDCAT_LEVEL = 3
                LEFT JOIN MST_PRDCAT AS bg
                ON      brand.PRDCAT_PAR = bg.PRDCAT_CD AND bg.PRDCAT_LEVEL = 2
                LEFT JOIN MST_PRDCAT AS ca
                ON      bg.PRDCAT_PAR = ca.PRDCAT_CD AND ca.PRDCAT_LEVEL = 1
                LEFT JOIN SET_REF
                ON      REF_PARAM = prd.PRD_CD
                        AND REF_TYPE = 'BrandNickName'
                ");

            string sWhere = " where ";
            if (!string.IsNullOrEmpty(condtion.ToString()))
            {

                sql.Append(sWhere + condtion);
            }
            DataSet ds;
            ds = SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql.ToString());
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                infos.Add(SetInfo(row));
            }
            return infos;
        }

        public Product SetInfo(System.Data.DataRow row)
        {
            Product pro = new Product();
            pro.PRD_CD = SqlHelper.GetString(row, "PRD_CD");
            pro.PRD_DESC2 = SqlHelper.GetString(row, "PRD_DESC2");
            pro.PRD_DESC1 = SqlHelper.GetString(row, "PRD_DESC1");
            pro.PRD_DESC = SqlHelper.GetString(row, "PRD_DESC");
            pro.CMCL_CD = SqlHelper.GetString(row, "CMCL_CD");
            pro.CMCL_NM_CHI = SqlHelper.GetString(row, "CMCL_NM_CHI");
            pro.CMCL_NM_ENG = SqlHelper.GetString(row, "CMCL_NM_ENG");
            pro.SHELF_LIFE = SqlHelper.GetString(row, "SHELF_LIFE");
            pro.PAC_CODE = SqlHelper.GetString(row, "TADE_PACKAGING_CODE");
            pro.PAC_INFO = SqlHelper.GetString(row, "TADE_PACKAGING_SEL_DESCR");
            pro.PCS_CU = SqlHelper.GetString(row, "PCS_CU");
            pro.CU_OUTER = SqlHelper.GetString(row, "CU_OUTER");
            pro.OUTER_CTN = SqlHelper.GetString(row, "OUTER_CTN");
            pro.CU_CTN = SqlHelper.GetString(row, "CU_CTN");
            pro.EAN_NO_EA = SqlHelper.GetString(row, "Barcode_CU(EA)");
            pro.EAN_NO_SU = SqlHelper.GetString(row, "Barcode_SU(Outer)");
            pro.EAN_NO_CARTON = SqlHelper.GetString(row, "Barcode_CU(CartON)");
            pro.VOLUME = SqlHelper.GetString(row, "VOLUME");
            pro.QLI = SqlHelper.GetString(row, "Qli");
            pro.CTN_PL = SqlHelper.GetString(row, "CTN_PL");
            pro.PRINT_NET_WEIGHT_CU = SqlHelper.GetString(row, "CU_PRINT_NET_WEIGHT");
            pro.PRINT_NET_WEIGHT_TU = SqlHelper.GetString(row, "TU_PRINT_NET_WEIGHT");
            pro.NET_WEIGHT_CU = SqlHelper.GetString(row, "CU_NET_WEIGHT");
            pro.NET_WEIGHT_TU = SqlHelper.GetString(row, "TU_NET_WEIGHT");
            pro.GROSS_WEIGHT_CU = SqlHelper.GetString(row, "CU_GROSS_WEIGHT");
            pro.GROSS_WEIGHT_TU = SqlHelper.GetString(row, "TU_GROSS_WEIGHT");
            pro.FISCAL_YEAR = SqlHelper.GetString(row, "FISCAL_YEAR");
            pro.SALES_ORG = SqlHelper.GetString(row, "SALES_ORG");
            pro.TYPE = SqlHelper.GetString(row, "TYPE");
            pro.QS_CIQ = SqlHelper.GetString(row, "QS_CIQ");
            pro.SOURCE = SqlHelper.GetString(row, "SOURCE");
            pro.PLANT = SqlHelper.GetString(row, "PLANT");
            pro.SUB_CLASS = SqlHelper.GetString(row, "SUBCLASS");
            pro.SUB_DESC = SqlHelper.GetString(row, "subc_desc");
            pro.BRAND_Code = SqlHelper.GetString(row, "brand");
            pro.BRAND_Desc = SqlHelper.GetString(row, "brand_desc");
            pro.BRAND_NickName_Code = SqlHelper.GetString(row, "BRAND_NickName_Code");
            pro.BRAND_NickName_Desc = SqlHelper.GetString(row, "BRAND_NickName_Desc");
            pro.BRAND_Group_Code = SqlHelper.GetString(row, "brandGroup");
            pro.BRAND_Group_Desc = SqlHelper.GetString(row, "brandGroup_desc");
            pro.Category_Code = SqlHelper.GetString(row, "category");
            pro.Category_DESC = SqlHelper.GetString(row, "category_desc");
            pro.Material_Status_Code = SqlHelper.GetString(row, "STATUS");
            pro.Material_Promotion_Variant_Code = SqlHelper.GetString(row, "PROMO_ID");

            return pro;
        }
    }
}
